# -*- coding: utf-8 -*-
#
# THIS FILE AIMS TO GENERATE INVOICE FILES
# IT CAN RUN ON ONLY WINDOWS
#

import win32com.client
import os, shutil
import time
import csv

USER = 'Brenden'
INVOICE_NO = 0
MONTH = 1
KO_NAME = 2
EN_NAME = 3
S_EN_NAME = 4
REQ_KO_DATE = 5
REQ_EN_DATE = 6
DUR_KO_DATE = 7
DUR_EN_DATE = 8
CLASS_COUNT = 9
PRICE_UNIT = 10
TOTAL_PRICE = 11
KR_TOTAL_PRICE = 12

def _kr(str):
    return unicode(str).encode('euc-kr')

def put_en_invoice(row, target_file):
    try :
        xl = win32com.client.Dispatch('Excel.Application')
        wb = xl.Workbooks.Open(target_file)
        sh = wb.worksheets(1)
        sh.Cells(3, 3).Value = 'Invoice No: emci-' + row[INVOICE_NO] + row[S_EN_NAME] + '11'
        sh.Cells(4, 3).Value = 'Invoice Date: ' + row[REQ_EN_DATE ]
        sh.Cells(18, 1).Value = row[EN_NAME]
        sh.Cells(27, 1).Value = row[DUR_EN_DATE]
        sh.Cells(27, 2).Value = row[PRICE_UNIT]
        sh.Cells(27, 3).Value = row[CLASS_COUNT]
        sh.Cells(27, 4).Value = row[TOTAL_PRICE]
        wb.Save()
        wb.Close()
    except:
        print 'Error put_en_invoice'
        wb.Save()
        wb.Close()


def put_kn_invoice(row, target_file):
    try :
        xl = win32com.client.Dispatch('Excel.Application')
        wb = xl.Workbooks.Open(target_file)
        sh = wb.worksheets(1)
        sh.Cells(3, 3).Value = u'발행번호: emci-' + row[INVOICE_NO] + row[S_EN_NAME] + '11'
        sh.Cells(4, 3).Value = _kr(u'발행일: ') + row[REQ_KO_DATE]
        sh.Cells(18, 1).Value = row[KO_NAME]
        sh.Cells(27, 1).Value = row[DUR_KO_DATE]
        sh.Cells(27, 2).Value = row[PRICE_UNIT]
        sh.Cells(27, 3).Value = row[CLASS_COUNT]
        sh.Cells(27, 4).Value = row[TOTAL_PRICE]
        wb.Save()
        wb.Close()
    except:
        print 'Error put_kn_invoice'
        wb.Save()
        wb.Close()

def put_product(row, target_file):
    try :
        xl = win32com.client.Dispatch('Excel.Application')
        wb = xl.Workbooks.Open(target_file)
        sh = wb.worksheets(1)
        sh.Cells(4, 3).Value = row[REQ_KO_DATE]
        sh.Cells(5, 1).Value = row[KO_NAME]
        sh.Cells(8, 7).Value = row[KR_TOTAL_PRICE]
        sh.Cells(8, 19).Value = row[TOTAL_PRICE]
        sh.Cells(11, 9).Value = row[CLASS_COUNT]
        sh.Cells(11, 11).Value = row[PRICE_UNIT]
        sh.Cells(11, 15).Value = row[TOTAL_PRICE]
        sh.Cells(27, 20).Value = USER
        wb.Save()
        wb.Close()
    except:
        print 'Error put_product'
        wb.Save()
        wb.Close()

def put_estimate(row, target_file):
    try :
        xl = win32com.client.Dispatch('Excel.Application')
        wb = xl.Workbooks.Open(target_file)
        sh = wb.worksheets(1)
        sh.Cells(4, 3).Value = row[REQ_KO_DATE]
        sh.Cells(5, 1).Value = row[KO_NAME]
        sh.Cells(8, 7).Value = row[KR_TOTAL_PRICE]
        sh.Cells(8, 19).Value = row[TOTAL_PRICE]
        sh.Cells(11, 9).Value = row[CLASS_COUNT]
        sh.Cells(11, 11).Value = row[PRICE_UNIT]
        sh.Cells(11, 15).Value = row[TOTAL_PRICE]
        sh.Cells(27, 20).Value = USER
        wb.Save()
        wb.Close()
    except:
        print 'Error put_estimate'
        wb.Save()
        wb.Close()

def make_file(row):
    FILE_NAMES = [
        'estimate.xls',
        'product.xls',
        'Tax_Invoice_en.xls',
        'Tax_Invoice_ko.xls',
        ]
    NEW_FILE_NAMES = [
        u'견적서.xls',
        u'납품서.xls',
        u'영문청구서.xls',
        u'국문청구서.xls',
        ]
    functions = [
        put_estimate,
        put_product,
        put_en_invoice,
        put_kn_invoice,
        ]
    name = row[1] + '_' + row[2]
    if os.path.isdir(name) == False:
        os.mkdir(name)
        print name
        target_dir = os.getcwd() + '\\' + name  + '\\'
        for i, file_name in enumerate(FILE_NAMES):
            source_file = 'c:\\invoice\\template\\' + file_name
            target_file = target_dir + row[2] + '_' + _kr(NEW_FILE_NAMES[i])
            print target_file
            shutil.copyfile(source_file, target_file)
            #time.sleep(1)
            functions[i](row, target_file)
        cmd = 'ConvertXLS /S"' + target_dir + '*.xls" ' + '/T"' + target_dir + '*.pdf" /C-1'
        pdf_convertor = open(target_dir + '\\converting_pdf.bat', 'w')
        pdf_convertor.write(cmd)
        pdf_convertor.close()
        print 'Finished'
    else:
        print 'Exist the same files'

def read_cvs():
    try :
        for row in csv.reader(open('invoice_detail.csv')):
            make_file(row)
    except:
        print 'Error Opening invoice file'

def main():
    read_cvs()

if __name__== '__main__':
    main()


